package com.kocel.block.repository.Impl;

import com.kocel.block.model.ViewRowMapper;
import com.kocel.block.model.smelt_pouring_history;
import com.kocel.block.repository.JdbcRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcRepositoryImpl implements JdbcRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    static final String viewSql = "select `p`.`FURNACE_CODE` AS `furnaceCode`,concat(`p`.`FURNACE_CODE`,`p`.`SUFFIX_CODE`) AS `packetCode`,`s`.`CUSTOM_NAME` AS `customName`,`s`.`SAND_BOX_NO` AS `sandBoxNo`,`s`.`SAND_BOX_NU` AS `sandBoxNu`,`s`.`POURING_SITE` AS `pouringSite`,`s`.`TEXTURE` AS `texture`,`s`.`PRODUCT_CODE` AS `productCode`,`s`.`PRODUCT_NAME` AS `productName`,`s`.`TECHNOLOGY` AS `technology`,`s`.`PLAN_END_TIME` AS `planEndTime`,`s`.`PLAN_ACTUAL_END_TIME` AS `planActualEndTime`,`s`.`GRADE_NUM` AS `gradeNum`,`s`.`WEIGHT` AS `weight`,`s`.`PLAN_NUMS` AS `planNums`,`s`.`POURING_WEIGHT` AS `pouringWeight`,substring_index(substring_index(`s`.`PIECE_NO`,',',(`b`.`help_topic_id` + 1)),',',-(1)) AS `pieceNo` from (((`smart`.`smelt_plan_info` `s` left join `smart`.`furnace_times_plan_info` `f` on((`f`.`UUID` = `s`.`FURNACE_PLAN_UUID`))) left join `smart`.`packet_times_plan_info` `p` on((`p`.`UUID` = `s`.`PACKET_PLAN_UUID`))) join `mysql`.`help_topic` `b` on((`b`.`help_topic_id` < ((length(`s`.`PIECE_NO`) - length(replace(`s`.`PIECE_NO`,',',''))) + 1)))) where ((`s`.`STATUS` = 'RELEASE') and (`s`.`FURNACE_PLAN_UUID` is not null) and (`s`.`PLAN_ACTUAL_END_TIME` is not null)) order by `s`.`PLAN_ACTUAL_END_TIME` desc";

    @Override
    public List<smelt_pouring_history> getSmelt_pouring_historyList(String meltingStove,String meltingWrap,String texture) {
        String sql = "select * from (" + viewSql + ") as smelt_pouring_history where ";
        if(!meltingWrap.equals("")){
            sql += "smelt_pouring_history.packetCode like '%" + meltingWrap + "%' ";

            if(!meltingStove.equals("") || !texture.equals("")) {
                sql += " or ";
            }
        }
        if(!meltingStove.equals("")){
            sql += "smelt_pouring_history.furnaceCode like '%" + meltingStove + "%' ";

            if(!texture.equals("")) {
                sql += " or ";
            }
        }
        if(!texture.equals("")){
            sql += "smelt_pouring_history.texture like '%" + texture + "%' ";
        }

        List<smelt_pouring_history> list = jdbcTemplate.query(sql, new ViewRowMapper());
        return list;
    }

    @Override
    public List<smelt_pouring_history> getSmelt_pouring_historyTop100() {
        String sql = "select * from (" + viewSql + ") as smelt_pouring_history LIMIT 0,100;";

        List<smelt_pouring_history> list = jdbcTemplate.query(sql, new ViewRowMapper());
        return list;
    }

    @Override
    public List<smelt_pouring_history> getSmelt_pouring_historyByMeltingWrap(String meltingWrap) {
        String sql = "select * from (" + viewSql + ") as smelt_pouring_history where smelt_pouring_history.packetCode like '%" + meltingWrap + "%'";

        List<smelt_pouring_history> list = jdbcTemplate.query(sql, new ViewRowMapper());
        return list;
    }

    @Override
    public List<smelt_pouring_history> getSmelt_pouring_historyByMeltingStove(String meltingStove) {
        String sql = "select * from (" + viewSql + ") as smelt_pouring_history where smelt_pouring_history.furnaceCode like '%" + meltingStove + "%'";

        List<smelt_pouring_history> list = jdbcTemplate.query(sql, new ViewRowMapper());
        return list;
    }
}
